const mysql = require('mysql2/promise');

async function testDatabase() {
    console.log('=================================');
    console.log('数据库完整性测试');
    console.log('=================================\n');

    const config = {
        host: 'localhost',
        user: 'root',
        password: 'Lycc.2025',
        database: 'fun_english_db',
        charset: 'utf8mb4'
    };

    let connection;
    let allPassed = true;

    try {
        connection = await mysql.createConnection(config);
        console.log('✅ 数据库连接成功\n');

        // 测试1: 检查表数量
        console.log('测试1: 检查数据表...');
        const [tables] = await connection.query('SHOW TABLES');
        console.log(`  表数量: ${tables.length} 个`);
        if (tables.length >= 13) {
            console.log('  ✅ 通过（应该>=13个表）\n');
        } else {
            console.log('  ❌ 失败（表数量不足）\n');
            allPassed = false;
        }

        // 测试2: 检查单词数量
        console.log('测试2: 检查单词数量...');
        const [words] = await connection.query('SELECT COUNT(*) as total FROM vocabulary');
        console.log(`  单词总数: ${words[0].total} 个`);
        if (words[0].total === 848) {
            console.log('  ✅ 通过（正好848个）\n');
        } else {
            console.log(`  ⚠️  差异: ${848 - words[0].total} 个\n`);
            allPassed = false;
        }

        // 测试3: 各年级单词分布
        console.log('测试3: 各年级单词分布...');
        const [gradeStats] = await connection.query(`
            SELECT grade, COUNT(*) as count 
            FROM vocabulary 
            GROUP BY grade 
            ORDER BY FIELD(grade, '三上', '三下', '四上', '四下', '五上', '五下', '六上', '六下')
        `);
        gradeStats.forEach(row => {
            console.log(`  ${row.grade}: ${row.count} 个单词`);
        });
        console.log('  ✅ 分布正常\n');

        // 测试4: 检查存储过程
        console.log('测试4: 检查存储过程...');
        const [procedures] = await connection.query("SHOW PROCEDURE STATUS WHERE Db = 'fun_english_db'");
        console.log(`  存储过程数量: ${procedures.length} 个`);
        procedures.forEach(proc => {
            console.log(`    - ${proc.Name}`);
        });
        if (procedures.length >= 2) {
            console.log('  ✅ 通过（应该>=2个存储过程）\n');
        } else {
            console.log('  ❌ 失败（存储过程不足）\n');
            allPassed = false;
        }

        // 测试5: 检查管理员账户
        console.log('测试5: 检查管理员账户...');
        const [admins] = await connection.query('SELECT username, role, status FROM admins');
        console.log(`  管理员数量: ${admins.length} 个`);
        admins.forEach(admin => {
            console.log(`    - ${admin.username} (${admin.role}) [${admin.status}]`);
        });
        if (admins.length >= 1 && admins.some(a => a.username === 'admin')) {
            console.log('  ✅ 通过（存在admin账户）\n');
        } else {
            console.log('  ❌ 失败（缺少admin账户）\n');
            allPassed = false;
        }

        // 测试6: 检查成就数据
        console.log('测试6: 检查成就数据...');
        const [achievements] = await connection.query('SELECT COUNT(*) as total FROM achievement_definitions');
        console.log(`  成就数量: ${achievements[0].total} 个`);
        if (achievements[0].total >= 16) {
            console.log('  ✅ 通过（应该>=16个成就）\n');
        } else {
            console.log('  ❌ 失败（成就数量不足）\n');
            allPassed = false;
        }

        // 测试7: 检查复合词
        console.log('测试7: 检查复合词导入...');
        const testWords = ['pencil box', 'teacher\'s desk', 'ping-pong', 'doing morning exercises'];
        let foundCount = 0;
        for (const word of testWords) {
            const [result] = await connection.query('SELECT word FROM vocabulary WHERE word = ?', [word]);
            if (result.length > 0) {
                foundCount++;
            }
        }
        console.log(`  复合词样本: ${foundCount}/${testWords.length} 找到`);
        if (foundCount >= 3) {
            console.log('  ✅ 通过（复合词导入正常）\n');
        } else {
            console.log('  ⚠️  部分复合词可能未导入\n');
        }

        // 最终结果
        console.log('=================================');
        if (allPassed) {
            console.log('✅ 所有测试通过！数据库完整性良好！');
        } else {
            console.log('⚠️  部分测试未通过，请检查上述失败项');
        }
        console.log('=================================\n');

    } catch (error) {
        console.error('❌ 测试过程出错:', error.message);
    } finally {
        if (connection) {
            await connection.end();
        }
    }
}

testDatabase();

